Cube Views

Cube View Connection Settings

When you create a new Cube View Connection, you can populate the following settings:

Property	Description
Name	The name is created for you if you select the Cube View first. If you type a name first and then select the Cube View, the typed name is retained.
Refers To	This setting specifies the starting cell or range in your spreadsheet for the Cube View. It establishes the default cell or range reference for the Cube View Connection.
Cube View	Click the ellipsis to look up and add a Cube View to make a Cube View Connection with.
Resize Initial Column Widths Using Cube View Settings	Selected by default, this option automatically re-sizes column widths based on the settings from the Cube View selected.
Insert or Delete Rows When Resizing Cube View Content	Select this option if you plan to stack everything vertically on your worksheet. This automatically adds or deletes rows as a Cube View expands and contracts.
Insert or Delete Columns When Resizing Cube View Content	Select this option if you plan to stack everything horizontally on your worksheet. This automatically adds or deletes columns as a Cube View expands and contracts.
Include Cube View Header	This option includes the header on the Cube View.
Retain Formulas in Cube View Content	This option allows you to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas before and after submission of the sheet or workbook.
Dynamically Evaluate Highlighted Cells	This option is only available if the previous option is selected. This highlights a cell if the formula reference has changed without having to refresh a spreadsheet.
Preserve Excel Format	This option enables you to preserve native Excel formatting changes made to Cube Views. When enabled, formatting changes made via native Excel formatting will be retained.
Add Parameter Selectors to Sheet	Selecting this option will generate parameter selectors for all parameters in the selected Cube View, including any dependent parameters. These parameter selectors can be drop-down menus or free-form cell inputs. This option must be selected during the creation process and cannot be added retroactively. To edit parameters added during the creation of a new Cube View Connection, navigate to OneStream > General > Parameters from the Excel or Spreadsheet ribbon. NOTE: If parameters are created using Add Parameter Selectors to Sheet, they will need to be manually deleted from the Parameters ribbon if the associated Cube View Connection is deleted. Deleting the connection alone will orphan the parameters that were added to the sheet.

Add a Cube View to Excel

Add a Cube View to an Excel sheet using the below steps:

Click Cube Views > Cube View Connections.
From this window, the cube views added to an Excel workbook can be managed. You can add, remove, edit, or go to styles. Click Add, to add a new Cube View.
Name the connection and then choose the Cube View.
Populate the following fields:
1. Resize Initial Column Widths Using Cube View Settings is the default setting. If you disable it, you can change the columns and save the Cube View. However, if you go back into the same Cube View Connection, the check box will be enabled and you’ll need to disable it again to keep your new cube view settings.
2. Insert or Delete Rows/Colums When Resizing Cube View Content: Select whether there needs to be inserted or deleted rows and/or columns when resizing. This setting will move around other content in the sheets if the size of the Cube View changed since the last refresh.
3. Include Cube View Header: Choose whether to add header rows to the spreadsheet.
4. Retain Formulas in Cube View Content allows you to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed the formulas will remain. If the value resulting from the value is different than the value of the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow. See Retain Formulas in Cube View Content in Excel and Spreadsheet.
  
  NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless Dynamically Highlighted Evaluated Cells is turned on.
5. Preserve Excel Format: This option enables you to preserve native Excel formatting changes made to Cube Views. When enabled, formatting changes made via native Excel formatting will be retained. See Preserve Formatting.
Click the OK button and then the Close button to view your Cube View.

After the Cube View is added, it will appear on the sheet. If formatting was applied to the Cube View, see Cube Views in Presentation, the formatting will come forward into the Excel sheet. Otherwise, apply Excel Styles. These styles are stored in the Excel sheet and can be copied between workbooks. For more information on Excel Styles, see Styles. If you enabled Preserve Excel Format, your formatting will remain. See Preserve Formatting.

NOTE: In order to copy Excel spreadsheet cells into a Data Explorer Grid on the web, click CTRL, select the cells desired, and then click CTRL-C. Navigate to the Data Explorer Grid, select a cell, and click CTRL-V, this will paste the cells into the grid. This can also be done from a Data Explorer Grid into an Excel Spreadsheet.

Retain Formulas in Cube View Content in Excel and Spreadsheet

Retain Formulas in Cube View Content allows you to form Cube View grids of data in Excel, using the Cube Views menu function, that can be linked to other Excel models for easy submission into OneStream. This feature allows formulas, (to writeable cells,) in Excel (or Spreadsheet) for an attached Cube View to be retained on submission and retrieval instead of being replaced with the value of the represented formula.

The Retain Formulas in Cube View Content feature, allows users to plan, budget or forecast and use the familiar functionality of Excel while still submitting data back to the OneStream database.

Use the Retain Formulas in Cube View Content feature to enter formulas in the Cube View (Excel or Spreadsheet) and retain those formulas pre and post submission of the sheet or workbook. When the sheet or workbook is refreshed, the formulas will remain. If the value resulting from the formula differs from the existing value in the OneStream database, the cell will initially become a dirty cell and will turn the cell format to yellow.

NOTE: When using external Excel workbooks, or after any updates to referenced sheets within the same workbook, you must Refresh Sheet to visualize the dirty cells and then Submit Sheet, unless you’ve turned on Dynamically Highlight Evaluated Cells in the cube view.

Retain Formulas in Cube View Content links to other Excel worksheets or worksheets in other Excel workbooks.

From the OneStream menu, select Cube Views > Cube View Connections.
Click Add in the Cube View Connection window or click Edit if you already have a cube view.
Click Retain Formulas in Cube View Content box and click the OK button.
Add the Cube View, if one is not already selected, and click the Close button.

Dynamically Highlight Evaluated Cells in Excel or Spreadsheet

When Retain Formulas in Cube View Content is enabled, the option to Dynamically Highlight Evaluated Cells becomes available to enable. When it’s enabled, every time you make a change to a cell in Excel or Spreadsheet that is referenced in a Cube View, the cell will immediately update and show the update with a change in color. This cell update is called a dirty cell, which indicates that the cell value is different from the information in the OneStream database.

Cube View Connection Dialog

Dynamically Highlight Evaluated Cells saves you a step because the cell changes without requiring a refresh. This feature evaluates all the cells in the spreadsheet and identifies the values in the cube view that have changed relative to its original value in the database.

Excel users who want to continue working in Excel to access can log in through the OneStream menu, update the cube view content and submit it to the database without leaving Excel. You can also perform these tasks in Spreadsheet within the application.

You can Retain Formulas in a Cube View Content that are related to values within a function, existing workbook, sheet, other sheets, in external workbooks, and in external renamed worksheets in Excel. Spreadsheet also offers this functionality, but it doesn’t allow you to point the cell references to external workbooks.

Click Refresh Sheet to see all changes within the cube view content and then click Submit Sheet or activate Dynamically Highlight Evaluated Cells and the cell updates automatically.

The number of cells with formulas in the cube view determines the amount of time it takes to update the cells. You can turn the feature on or off and only use Refresh Sheet to update the values in the cells. Changes will show very quickly, no matter the size of the worksheet, when using Spreadsheet.

Using Retain Formulas and Dynamically Highlight Evaluated Cells

You can also use retain formulas and dynamically highlighted evaluated cells within a cube view to automatically display updated values in an existing workbook, a sheet or sheets, external workbooks, and external renamed worksheets in Excel. You can also do this in Spreadsheet within the OneStream application, however, you can’t point the cell references to external workbooks.

In Excel, go to the OneStream menu and Log on.
Click Cube Views > Cube View Connections.
Click the Add button.
In the Cube View Connection window, click the ellipsis next to the Cube View field.
Select your choice and click the OK button.
Click Retain Formulas in Cube View Content to activate Dynamically Highlight Evaluated Cells.
Then click Dynamically Highlight Evaluated Cells so you can see the changes as they are made.
Even if you don’t activate the dynamically highlight evaluated cells feature, you can click Refresh Sheet after you make changes to see them.
If you’re prompted, click OK once you’ve selected the parameters for the cube view.
Once the cube view has been added, you can click Edit to review, if needed.
Make changes to the sheet and press <Enter> to see the updated cell, which will change from white to yellow.
Click Submit Sheet to automatically save changes to the database.

Use Cases

These use cases are for both Excel and Spreadsheet unless otherwise noted.

The placing of formulas or cell references. Retain Formulas can reference the following types of formulas. In all instances the formula will stay after refresh and/or submission.

Cell References of individual cells of data on the same sheet.

Cell References to a cell on the same sheet, factored by another value.

Cell References to cells on other sheets. These can also be factored by another value as well.

Referenced cell(s) on another saved workbook can also be factored by another value.

NOTE: This applies to Excel only.

Best Practices

Well-Formed Grid

It is suggested to create a “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views. When using this “Well-Formed Grid” (Root.List or Comma Separated List) in Cube Views, the Excel/SpreadSheet relative (=C2) and absolute formulas (=$C$2) will be retained.

However, when using these relative and absolute formulas within an Excel and Spreadsheet formula, users can use either the cell reference or text within the formula depending upon how members will be added or removed:

=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will work in a List or Comma-Separated list (Well-formed grid) when Accounts are added to the end.
=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a case when a Member of a Row is moved up or down.

Member Expansion Functions

When using Member Expansion Functions in Cube Views for Excel and SpreadSheet, the cell being referenced within the function (Vlookup, etc), will need to be adjusted and/or referenced as text.

=VLOOKUP("52000 - Promotions",Sheet1!A:B,2,FALSE) will work in a Dynamic or when a Member of a Row is moved.
=VLOOKUP(D30,Sheet1!A:B,2,FALSE) will NOT work in a Dynamic or when a Member of a Row is moved as this is using the cell ref of D30.

Other Considerations

Deselecting the Retain Formulas for Cube View Content will eliminate all formulas that were established /existed on the Cube View grid.
Pivoting the existing Dimensions of the Cube View will break formulas.
Changing the “structure” of the Cube View grid in the rows or columns will also break the formulas. For example; If you have Account, Entity, UD3 as the dimensions used in the row and switch it to UD3, Entity, Account, it will break the formulas.
Users can change the POV to select a new dimension. This will change the Cube View results but retain the existing formulas that were established. The user at this point can choose to utilize the existing formulas, modify or delete. If the original formulas are modified or deleted, the last action will be saved.
Linking a white cell (writeable cell) to another cell in a different workbook will work ONLY in Excel and NOT in Spreadsheet.
Prior to establishing links to an external workbook, the user should save the external workbook being referenced.
When the user renames or saves as the (referenced) file, the user will need to update the links to the newly created file. Updating the links on the spreadsheet should be done BEFORE doing a refresh or submit.
Formulas with cell references (VLOOKUP, INDEX(MATCH(, etc) that return errors (#N/A, #ERROR, etc) or non-numeric data will not retain the formula and return to its original value from the Cube View ; this error text cannot be converted into a number so the formulas will not retain.
If a Dimension Member Name is renamed; i.e.; “52200 – Rent” is now “52200 – Rent Commercial”, the formula will break.